﻿using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading;
using Common.Utilities.Dispatcher;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

namespace Workflows.Components.SchemaUtil
{
    /// <summary>
    /// 
    /// </summary>
    public class CLRScripterWorker:IWorker 
    {
        #region Implementation of IWorker

        private Guid _JobTicketID;

        private string _WorkflowName;

        private string _ComponentName;

        private Thread _WorkerThread;

        private CLRScripterInstruction _Instruction;

        private CLRScripterOutput _Output;

        private RunContext _Context;

        /// <summary>
        /// handler to worker thread
        /// </summary>
        public Thread WorkerThread
        {
            get
            {
                return _WorkerThread;
            }
            set
            {
                _WorkerThread = value;
            }
        }

        /// <summary>
        /// allow instruction to be changed at runtime
        /// </summary>
        public InstructionBase Instruction
        {
            get
            {
                return _Instruction;
            }
        }

        /// <summary>
        /// output
        /// </summary>
        public ResultBase Output
        {
            get
            {
                return _Output;
            }
        }

        /// <summary>
        /// allow worker to be created based on component type
        /// </summary>
        public Type ComponentType
        {
            get
            {
                return typeof (CLRScripterComponent);
            }
        }

        /// <summary>
        /// runtime context retrieved from appl domain
        /// </summary>
        public RunContext Context
        {
            get
            {
                return _Context;
            }
            set
            {
                _Context = value;
            }
        }

        public string WorkflowName
        {
            get
            {
                return _WorkflowName;
            }
        }

        public string ComponentName
        {
            get
            {
                return _ComponentName;
            }
        }

        public Guid JobTicketID
        {
            get
            {
                return _JobTicketID;
            }
        }

        /// <summary>
        /// this must be called before actual work
        /// </summary>
        /// <param name="ticketGuid"></param>
        /// <param name="request"></param>
        /// <param name="workflowName"></param>
        /// <param name="componentName"></param>
        public void SetInstruction(Guid ticketGuid, InstructionBase request, string workflowName, string componentName)
        {
            this._JobTicketID = ticketGuid;
            this._Instruction = (CLRScripterInstruction) request;
            this._WorkflowName = workflowName;
            this._ComponentName = componentName;
        }

        /// <summary>
        /// this is called within subflow, where runtime context is also initialized
        /// </summary>
        /// <param name="context"></param>
        /// <param name="ticketGuid"></param>
        /// <param name="request"></param>
        /// <param name="workflowName"></param>
        /// <param name="componentName"></param>
        public void SetInstruction(RunContext context, Guid ticketGuid, InstructionBase request, string workflowName, string componentName)
        {
            this._Context = context;
            this._JobTicketID = ticketGuid;
            this._Instruction = (CLRScripterInstruction)request;
            this._WorkflowName = workflowName;
            this._ComponentName = componentName;
        }

        /// <summary>
        /// this is called synchronously, used in testing mode
        /// </summary>
        public void DoWork()
        {
            if (this.WorkerStartedEvent != null)
                this.WorkerStartedEvent(this._JobTicketID, this, this.GetType(), this._Instruction);
            this.GenerateCLRScript();
            this._WorkerThread = Thread.CurrentThread;
        }

        /// <summary>
        /// this is called by job dispatcher
        /// </summary>
        public void DoWorkAsync()
        {
            if (this.WorkerStartedEvent != null)
                this.WorkerStartedEvent(this._JobTicketID, this, this.GetType(), this._Instruction);
            this._WorkerThread = new Thread(new ThreadStart(this.GenerateCLRScript));
            this._WorkerThread.Start();
        }

        /// <summary>
        /// this is called by job monitor/manager
        /// </summary>
        public void CancelWork()
        {
            if(this._WorkerThread!=null && this._WorkerThread.IsAlive)
            {
                this._WorkerThread.Abort();

                if (this.WorkerCancelledEvent != null)
                    this.WorkerCancelledEvent(this._JobTicketID, this.GetType(), this._Instruction);
            }
        }

        public event WorkerStarted WorkerStartedEvent;
        public event WorkerFinished WorkerFinishedEvent;
        public event WorkerHasError WorkerErredEvent;
        public event WorkerCancelled WorkerCancelledEvent;
        public event WorkerStatusChanged WorkerStatusChangedEvent;

        #endregion

        #region internal class 
        internal class replacement
        {
            public String pattern;
            public String value;
            public replacement(String pattern, String value)
            {
                this.pattern = pattern;
                this.value = value;
            }
        }

        internal class sqlAssemblyRef
        {
            public SqlAssembly sqlAssembly;
            public sqlAssemblyRef(SqlAssembly sqlAssembly)
            {
                this.sqlAssembly = sqlAssembly;
            }
            public override string ToString()
            {
                return sqlAssembly.Name;
            }
        }

        internal class databaseRef
        {
            public databaseRef(Database database)
            {
                this.database = database;
            }
            public Database database;
            public override string ToString()
            {
                return database.Name;
            }
        }

        internal class moduleName
        {
            internal String name;
            internal String schema;
            internal moduleName(String name, String schema)
            {
                this.name = name;
                this.schema = schema;
            }
        }
        #endregion

        #region work
        private void GenerateCLRScript()
        {
            try
            {
                Server sqlInstance = new Server(this._Instruction.DbServer);
                Database db = sqlInstance.Databases[this._Instruction.DatabaseName];
                SqlAssembly sqlAsm = db.Assemblies[this._Instruction.CLRAssemblyName];

                // make a list of replacements
                // this is used to complete the SQL query that will make an
                // ordered list of assembles, including referenced assemblies,
                // that will be scripted out. Note that referenced assemblies
                // must be scripted before the assemblies that reference them
                replacement[] replacements1 =
                    new replacement[]
                        {
                            new replacement("%assemblies%", "'" + this._Instruction.CLRAssemblyName + "'"),
                            new replacement("%dbname%", this._Instruction.DatabaseName)
                        };
                // this builds the query that will return the assembly names in order
                String assemblyBatch =
                    GetBatch(string.Format("{0}.LoadOrder.sql", this.GetType().Assembly.GetName().Name), replacements1);
                ServerConnection conn = sqlInstance.ConnectionContext;
                // use the connection that SMO has already built to execute
                // the query that returns the orded assembly list
                // there is one table in this dataset with the assembly list in it
                DataSet assemblySet = conn.ExecuteWithResults(assemblyBatch);
                // working varible to hold an assembly, i.e. the SMO assembly object
                // go through the rows in the table to get the assemblies, in order
                databaseRef dbref=new databaseRef(db);
                ScriptingOptions sopts=new ScriptingOptions();
                StringBuilder sb=new StringBuilder();
                foreach (DataRow row1 in assemblySet.Tables[0].Rows)
                {
                    // look up the assembly by name
                    sqlAsm = dbref.database.Assemblies[row1[0].ToString()];
                    // SMO returns a script as a string collection
                    // This also writes to a file, if the script options specify a file name
                    StringCollection sc = sqlAsm.Script(sopts);
                    if (sopts.FileName != null)
                    {
                        // apped to file after initial output
                        sopts.AppendToFile = true;
                    }
                    // the string collection will be empty if the scripting
                    // options specify file only output
                    foreach (String s in sc)
                    {
                        sb.Append(s + "\r\nGO\r\n");
                    }
                }

                // go through each assembly and script out its CLR stored procedures
                // CLR functions, user defined types and aggregates
                foreach (DataRow row1 in assemblySet.Tables[0].Rows)
                {
                    // get a reference to the assembly, i.e. a SMO assembly object
                    sqlAsm = dbref.database.Assemblies[row1[0].ToString()];
                    // get a ref to the database the  assembly belongs to
                    db = sqlAsm.Parent;
                    // make the replacements for the query that will find the user defined
                    // functions that belong to this assembly
                    replacement[] replacements = new replacement[] {new replacement("%dbname%", db.Name)};
                    DataSet set =
                        conn.ExecuteWithResults(
                            GetBatch(
                                string.Format("{0}.ExternalFunction.sql", this.GetType().Assembly.GetName().Name),
                                replacements));
                    // get all names of the functions
                    // note: SQL Server refers to functions, stored procedures, etc a Modules
                    moduleName[] udfs = new moduleName[set.Tables[0].Rows.Count];
                    int index = 0;
                    foreach (DataRow row in set.Tables[0].Rows)
                    {
                        // each module has a schema and a name
                        udfs[index] = new moduleName(row[1].ToString(), row[0].ToString());
                        index = index + 1;
                    }
                    // get all the functions
                    set =
                        conn.ExecuteWithResults(
                            GetBatch(
                                string.Format("{0}.ExternalProc.sql", this.GetType().Assembly.GetName().Name),
                                replacements));
                    moduleName[] procs = new moduleName[set.Tables[0].Rows.Count];
                    index = 0;
                    // build up stored procedure names
                    foreach (DataRow row in set.Tables[0].Rows)
                    {
                        procs[index] = new moduleName(row[1].ToString(), row[0].ToString());
                        index++;
                    }

                    foreach (moduleName f in udfs)
                    {
                        // get user defined function
                        UserDefinedFunction udf = db.UserDefinedFunctions[f.name, f.schema];
                        if (udf != null &&
                            udf.AssemblyName == sqlAsm.Name)
                        {
                            // script out the function
                            foreach (String s in udf.Script(sopts))
                            {
                                sb.Append(s + "\r\nGO\r\n");
                            }
                        }
                    }
                    foreach (moduleName p in procs)
                    {
                        // get stored procedur
                        StoredProcedure proc = db.StoredProcedures[p.name, p.schema];
                        if (proc != null &&
                            proc.AssemblyName == sqlAsm.Name)
                        {
                            // script out stored procedure
                            foreach (String s in proc.Script(sopts))
                            {
                                sb.Append(s + "\r\nGO\r\n");
                            }
                        }
                    }
                    // find all the user defined aggregates
                    UserDefinedAggregate[] ags = new UserDefinedAggregate[db.UserDefinedAggregates.Count];
                    db.UserDefinedAggregates.CopyTo(ags, 0);
                    foreach (UserDefinedAggregate a in ags)
                    {
                        if (a.AssemblyName == sqlAsm.Name)
                        {
                            // script out the user defined aggregate
                            foreach (String s in a.Script(sopts))
                            {
                                sb.Append(s + "\r\nGO\r\n");
                            }
                        }
                    }
                    // find all the user defined types
                    foreach (UserDefinedType t in db.UserDefinedTypes)
                    {
                        if (t.AssemblyName == sqlAsm.Name)
                        {
                            // script out the user defined type
                            foreach (String s in t.Script(sopts))
                            {
                                sb.Append(s + "\r\nGO\r\n");
                            }
                        }
                    }
                }

                this._Output = new CLRScripterOutput(
                    this._Instruction.OwnerComponentID, this._JobTicketID, sb.ToString());

                if(this.WorkerFinishedEvent!=null)
                {
                    this.WorkerFinishedEvent(this._JobTicketID, this.GetType(), this._Instruction, this._Output);
                }
            }
            catch (Exception ex)
            {
                if (this.WorkerErredEvent != null)
                    this.WorkerErredEvent(this._JobTicketID, this.GetType(), this._Instruction, ex.ToString(), true);
            }
        }

        /// <summary>
        /// gets an embedded resourse and replaces patterns in it
        /// this is used to get sql queries and patch them up with 
        /// the right database name, assemblies, etc
        /// </summary>
        /// <param name="name"></param>
        /// <param name="replacements"></param>
        /// <returns></returns>
        private string GetBatch(string name, replacement[] replacements)
        {
            String batch = null;
            StreamReader sr = new StreamReader(
                Assembly.GetExecutingAssembly().GetManifestResourceStream(name));
            batch = sr.ReadToEnd();
            sr.Close();
            foreach (replacement replace in replacements)
            {
                batch = Regex.Replace(batch, replace.pattern, replace.value, RegexOptions.Singleline);
            }
            return batch;
        }
        #endregion
    }
}
